unit View;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls, DB, ZAbstractRODataset,
  ZAbstractDataset, ZAbstractTable, ZDataset, ZConnection, fEdit, frmTanda, Setting;

type
  TPemilih = record
     ID:String;
     NIK:String;
     NKK:String;
     NAMA:String;
     TEMPAT:String;
     TANGGAL:String;
     IBU:String;
     AGAMA:String;
     KAWIN:String;
     KELAMIN:String;
     KELAINAN:String;
     ALAMAT:String;
     KOTA:String;
     KEC:String;
     DESA:String;
     RW:String;
     RT:String;
     DUSUN:String;
     KET:String;
  end;
  TTPemilih = array of TPemilih;
  TPenyimpan = record
     ID:String;
     NAMA:String;
  end;
  TTPenyimpan = array of TPenyimpan;
  TDpt = record
     KODE:String;
     NAMA:String;
  end;
  TTDpt = array of TDpt;
  TTabel = record
     ID:String;
     TABEL:String;
     COUNT:Integer;
  end;
  TTTabel = array of TTabel;
  TGanda = record
     ID1:String;
     ID2:String;
     KET:Integer;
  end;
  TTGanda = array of TGanda;
  TForm2 = class(TForm)
    ZConnection1: TZConnection;
    ZTable1: TZTable;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    StringGrid1: TStringGrid;
    ZTable2: TZTable;
    ZQuery1: TZQuery;
    lblHal: TLabel;
    btnCari: TButton;
    txtHal: TEdit;
    btnTanda: TButton;
    btnPrev: TButton;
    btnNext: TButton;
    btnEdit: TButton;
    ckDataGanda: TCheckBox;
    lblProses: TLabel;
    StringGrid2: TStringGrid;
    ZQuery2: TZQuery;
    btnBersih: TButton;
    btnTanda2: TButton;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    cbGanda: TComboBox;
    Button1: TButton;
    Button2: TButton;
    txtNama: TEdit;
    btnCarinama: TButton;
    GroupBox3: TGroupBox;
    GroupBox4: TGroupBox;
    GroupBox5: TGroupBox;
    Label4: TLabel;
    btnRefresh: TButton;
    btnEdit2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure btnCariClick(Sender: TObject);
    procedure btnHalClick(Sender: TObject);
    procedure txtHalKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btnTandaClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnPrevClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure StringGrid1Click(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure ckDataGandaClick(Sender: TObject);
    procedure btnTanda2Click(Sender: TObject);
    procedure StringGrid2Click(Sender: TObject);
    procedure btnBersihClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure btnCarinamaClick(Sender: TObject);
    procedure txtNamaChange(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject);
    procedure btnEdit2Click(Sender: TObject);

  private
    procedure FetchDB();
    procedure TampilHalaman(Hal:Integer = 1);
    procedure getPage;
    procedure DeletePemilih(var X: TTPemilih; const Index: Integer);
    function getIdsimpan(tabel: String): String;
    procedure AutoSizeCol(Grid: TStringGrid; Column: integer);
    function getSimpan(id: String): String;
    function getDesa(kode: String): String;
    function getKabkot(kode: String): String;
    function getKec(kode: String): String;
    procedure GetData(Hal: Integer);
    procedure FetchGanda;
    function getTabel(id: String): String;
    function getIduser(id: String): String;
    function cekBGanda(id: String): Boolean;
    procedure showGanda(idganda: String);
    procedure TampilHalamanGanda(Hal: Integer = 1);
    procedure bersihdata(var id1: String; id2: String; idg:String);
    procedure FetchDBNama;
    procedure GetDataNama(Hal: Integer);
    { Private declarations }
  public
    { Public declarations }
    Procedure sizeMove (var msg: TWMSize); message WM_SIZE;
  end;

var
  Form2: TForm2;
  cnama:Boolean;
  sKabkot:String;
  sKec:String;
  sDesa:String;
  cRow:Integer; //currentrow
  dRow:Integer; //data row
  cHal:Integer; //current halaman
  jHal:Integer; //jumlah halaman
  bHal:Integer; //batas per halaman
  bPemilih: TTPemilih;
  bPenyimpan:TTPenyimpan;
  bDesa:TTDpt;
  bKec:TTDpt;
  bKabkot:TTDpt;
  bTabel:TTTabel;
  bGanda:TTGanda;
  bIdganda:array of String;
  bGridganda:TTPenyimpan;
  fData:Boolean;
  fEdit:TForm3;
  fTanda:TForm5;
  ptable,ptable2:String;
  pid,pid2,tid,tid2:String;

implementation

{$R *.dfm}

procedure TForm2.bersihdata(var id1:String; id2:String; idg:String);
var
  temp:TStrings;
  tabel,idus,que:String;
  ada:Boolean;
  i:Integer;
begin
  temp := TStringList.Create; //1
  try
    ExtractStrings(['-'], [], PChar(id1), temp);
     tabel := temp[0];
     idus := temp[1];
  finally
    temp.Free;
  end;
  with ztable1 do begin
    Active:=false;
    TableName:=getSimpan(tabel);
    Filter:='ID_USER='+idus;
    filtered:=true;
    Active:=true;
    first;
  end;
  temp := TStringList.Create; //2
  try
    ExtractStrings(['-'], [], PChar(id2), temp);
     tabel := temp[0];
     idus := temp[1];
  finally
    temp.Free;
  end;
  with ztable2 do begin
    Active:=false;
    TableName:=getSimpan(tabel);
    Filter:='ID_USER='+idus;
    filtered:=true;
    Active:=true;
    first;
  end;
  if (ztable1.Fields[0].AsString<>'') and (ztable2.Fields[0].AsString<>'') then begin
    ada:=true;
    for I := 1 to 15 do begin
      if (ztable1.Fields[i].AsString<>ztable2.Fields[i].AsString) then
        ada:=false;
    end;
    if ada=true then begin
      with zquery2 do begin
        active:=false;
        sql.Clear;
        que:='DELETE FROM '+getSimpan(tabel)+' WHERE ID_USER='+idus;
        sql.Text:=que;
        ExecSQL;
        active:=false;
        sql.Clear;
        que:='DELETE FROM data_ganda WHERE ID='+idg;
        sql.Text:=que;
        ExecSQL;
      end;
    end;
  end;
end;

procedure TForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
  Finalize(bKabkot);
  Finalize(bKec);
  Finalize(bDesa);
  Finalize(bPemilih);
  Finalize(bPenyimpan);
end;
procedure TForm2.btnBersihClick(Sender: TObject);
var
  id,id1,id2,idg,que:String;
begin
  if getidsimpan('pemilih_'+sDesa)<>'' then begin
    id:=getidsimpan('pemilih_'+sDesa)+'-%';
    with zquery1 do begin
      Active:=false;
      sql.Clear;
      que:='SELECT * FROM data_ganda WHERE ID_USER1 LIKE '+QuotedStr(id)+
      ' AND ID_USER2 LIKE'+QuotedStr(id);
      sql.Text:=que;
      Active:=true;
    end;
    if (ZQuery1.RecordCount>0) then begin
      ZQuery1.First;
      while (not ZQuery1.Eof) do begin
        id1:=ZQuery1.FieldByName('ID_USER1').AsString;
        id2:=ZQuery1.FieldByName('ID_USER2').AsString;
        idg:=ZQuery1.FieldByName('ID').AsString;
        bersihdata(id1,id2,idg);
        ZQuery1.Next;
      end;
      showmessage('Data sudah bersih');
      FetchDB;
    end;
  end
    else
  ShowMessage('Desa tidak terdapat');
end;

procedure TForm2.btnCariClick(Sender: TObject);
begin
  cnama:=false;
  if (ckDataGanda.Checked=False) then begin
    FetchDB;
  end
    else
  begin
    if cbGanda.ItemIndex>-1 then
      FetchGanda
    else
      ShowMessage('Pilih tingkatan data ganda dahulu');
  end;
  btnEdit.Enabled:=False;
  btnTanda.Enabled:=False;
end;

procedure TForm2.btnCarinamaClick(Sender: TObject);
begin
  cnama:=true;
  FetchDBNama;
  btnEdit.Enabled:=False;
  btnTanda.Enabled:=False;
end;

procedure TForm2.DeletePemilih(var X: TTPemilih; const Index: Integer) ;
begin
   if Index > High(X) then Exit;
   if Index < Low(X) then Exit;
   if Index = High(X) then
   begin
     SetLength(X, Length(X) - 1) ;
     Exit;
   end;
   Finalize(X[Index]) ;
   System.Move(X[Index+1], X[Index],(Length(X) - Index -1) * SizeOf(TPemilih) + 1) ;
   SetLength(X, Length(X) - 1);
   StringGrid1.RowCount:=StringGrid1.RowCount-1;
end;

procedure TForm2.btnTandaClick(Sender: TObject);
var
  pos:Integer;
  tots:Integer;
  ID:String;
  tabel:String;
  que:String;
begin
  {pos:=StringGrid1.Row;
  //getID
  tabel:=Copy(bPemilih[pos].ID,1,16);
  tots:=Length(bPemilih[pos].ID)-16;
  ID:=Copy(bPemilih[pos].ID,17,tots);
  //get tabel
  //get ID di tabel
  //query
  with ZQuery1 do
    begin
      Active := False;
      que:='DELETE FROM '+tabel+' WHERE ID_USER='+ID;
      SQL.Text := que;
      ExecSQL;
    end;
  //hapus array
  ShowMessage('Pemilih telah dihapus');}
  //hati-hati ganti
  {tid:=getIdsimpan(ptable);
  if (cekBGanda(tid+'-'+pid)) then
    fTanda := TForm5.Create(Application,ptable,pid,true,tid,1)
  else
    fTanda := TForm5.Create(Application,ptable,pid,false,tid,1)}
end;

procedure TForm2.Button1Click(Sender: TObject);
var
  buttonSelected : Integer;
  que:string;
begin
  buttonSelected := MessageDlg('Apakah anda yakin akan menghilangkan tanda desa ini?',mtConfirmation , mbOKCancel, 0);
  if buttonSelected = mrOK  then begin
    with ZQuery1 do begin
      active:=false;
      sql.Clear;
      que:='UPDATE '+ptable+' SET STATUS2="0" WHERE ID_USER='+pid;
    end;
    ShowMessage('Tanda telah dihilangkan');
  end;
end;

procedure TForm2.Button2Click(Sender: TObject);
var
  buttonSelected : Integer;
  que:string;
begin
  buttonSelected := MessageDlg('Apakah anda yakin akan menghilangkan tanda desa ini?',mtConfirmation , mbOKCancel, 0);
  if buttonSelected = mrOK  then begin
    with ZQuery1 do begin
      active:=false;
      sql.Clear;
      que:='UPDATE '+ptable2+' SET STATUS2="0" WHERE ID_USER='+pid2;
    end;
    ShowMessage('Tanda telah dihilangkan');
  end;
end;

Procedure TForm2.sizeMove (var msg: TWMSize);
begin
 inherited;
 if (Self.ComboBox1.ItemIndex>-1) then begin
   if (msg.SizeType = SIZE_MAXIMIZED) OR (msg.SizeType = SIZE_RESTORED) then begin
     GroupBox1.Width:=Width-30;
     GroupBox2.Width:=Width-30;
     StringGrid1.Width:=Width-40;
     StringGrid2.Width:=Width-40;
   end;
 end;
end;

procedure TForm2.btnEdit2Click(Sender: TObject);
begin
  tid2:=getIdsimpan(ptable2);
//  showmessage(ptable2+'-'+pid2+'-'+tid2);
  fEdit := TForm3.Create(Application,ptable2,pid2,tid2,True,2,ptable,pid,tid);
end;

procedure TForm2.btnEditClick(Sender: TObject);
begin
  tid:=getIdsimpan(ptable);
//  showmessage(ptable+'-'+pid+'-'+tid);
  if (cekBGanda(tid+'-'+pid)) then
//    fEdit := TForm3.Create(Application,ptable,pid,tid,true,1)
  else
  //  fEdit := TForm3.Create(Application,ptable,pid,tid,false,1);
end;

procedure TForm2.btnHalClick(Sender: TObject);
begin
  getPage;
end;

procedure TForm2.btnNextClick(Sender: TObject);
begin
  TampilHalaman(cHal+1);
  txtHal.Text:=IntToStr(cHal);
end;

procedure TForm2.btnPrevClick(Sender: TObject);
begin
  TampilHalaman(cHal-1);
  txtHal.Text:=IntToStr(cHal);
end;

procedure TForm2.btnRefreshClick(Sender: TObject);
begin
  cnama:=false;
  FetchDB;
  btnEdit.Enabled:=False;
  btnTanda.Enabled:=False;
end;

procedure TForm2.btnTanda2Click(Sender: TObject);
begin
  tid2:=getIdsimpan(ptable2);
//  fTanda := TForm5.Create(Application,ptable2,pid2,True,tid2,2,ptable,pid,tid);
end;

procedure TForm2.ckDataGandaClick(Sender: TObject);
begin
  if (ckDataGanda.Checked) then begin
    CheckBox1.Enabled:=false;
    CheckBox4.Enabled:=false;
    CheckBox3.Enabled:=false;
    CheckBox2.Enabled:=false;
    cbGanda.Enabled:=true;
  end
    else
  begin
    cbGanda.Enabled:=false;
    CheckBox1.Enabled:=true;
    CheckBox4.Enabled:=true;
    CheckBox3.Enabled:=true;
    CheckBox2.Enabled:=true;
  end;
end;

procedure TForm2.ComboBox1Change(Sender: TObject);
var
  pilihan:String;
begin
  if (ComboBox1.ItemIndex > 0) then
  begin
    pilihan := ComboBox1.Items[ComboBox1.ItemIndex];
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kabkot';
        Filter := 'TAMPIL = '+QuotedStr(pilihan);
        Filtered := True;
        Active := True;
        First;
        sKabkot := FieldByName('KODE').AsString;
        //cb2
        Active := False;
        TableName := 'kecamatan';
        Filter := 'KODE_KABKOT = '+QuotedStr(sKabkot);
        Filtered := True;
        Active := True;
        First;
      end;
    ComboBox2.Clear;
    ComboBox3.Clear;
    ComboBox2.Items.Add('SEMUA KECAMATAN');
    while (not ZTable1.Eof) do
      begin
        ComboBox2.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    sKabkot:='';
    ComboBox2.Clear;
    ComboBox3.Clear;
  end;
  sKec:='';
  sDesa:='';
  btnBersih.Enabled:=false;
  btnCari.Enabled:=True;
  btnRefresh.Enabled:=true;
end;

procedure TForm2.ComboBox2Change(Sender: TObject);
var
  pilihan:String;
begin
  if (ComboBox2.ItemIndex > 0) then
  begin
    pilihan := ComboBox2.Items[ComboBox2.ItemIndex];
    ZTable1.Active := False;
    ZTable1.TableName := 'kecamatan';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(pilihan);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sKec := ZTable1.FieldByName('KODE_KECAMATAN').AsString;
    //cb3
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'KODE_KECAMATAN = '+QuotedStr(sKec);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    ComboBox3.Clear;
    ComboBox3.Items.Add('SEMUA DESA');
    while (not ZTable1.Eof) do
      begin
        ComboBox3.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    sKec:='';
    ComboBox3.Clear;
  end;
  sDesa:='';
  btnBersih.Enabled:=false;
end;

procedure TForm2.ComboBox3Change(Sender: TObject);
var
  desa:String;
begin
  if (ComboBox3.ItemIndex > 0) then
  begin
    desa := ComboBox3.Items[ComboBox3.ItemIndex];
    //
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(desa)+' AND KODE_KABKOT = '+QuotedStr(sKabkot);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sDesa := ZTable1.FieldByName('KODE_DESA').AsString;
    btnBersih.Enabled:=true;
  end
    else
  begin
    sDesa:='';
    btnBersih.Enabled:=false;
  end;
end;

procedure TForm2.FetchDB();
var
  kode:String;
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tblName:String;
  i:Integer;
  count:Integer;
  gdata:Boolean;
  que:String;
begin
  lblProses.Caption:='Harap tunggu sedang proses';
  lblHal.Caption:='-';
  gdata:=False;
  for i := 1 to StringGrid1.RowCount - 1
  do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  if (sDesa<>'') then
    begin
      kode:=sDesa;
    end
  else if (sKec<>'') then
    begin
      kode:=sKec;
    end
  else if (sKabkot<>'') then
    begin
      kode:=sKabkot;
    end
  else
    begin
      kode:='';
    end;
  if (kode='') then
    query:='1'
  else
    query:='NAMA LIKE "pemilih_'+kode+'%"';
  ZQuery1.Active := False;
  query:= 'SELECT * FROM penyimpan WHERE '+query;
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //dapat tabel yang di simpan
  SetLength(bTabel,ZQuery1.RecordCount);
  i:=0;
  count:=0;
  while (not ZQuery1.Eof) do
    begin
      gdata:=True;
      ZQuery2.Active := False;
      tblName:=ZQuery1.FieldByName('NAMA').AsString;
      ZQuery2.SQL.Clear;
      que:='SELECT COUNT(*) FROM '+tblname;
      if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
        que:=que+' WHERE ';
        if (CheckBox1.Checked) then que:=que+'STATUS2='+QuotedStr('1')+' OR ';
        if (CheckBox2.Checked) then que:=que+'STATUS2='+QuotedStr('2')+' OR ';
        if (CheckBox3.Checked) then que:=que+'STATUS2='+QuotedStr('4')+' OR ';
        if (CheckBox4.Checked) then que:=que+'STATUS2='+QuotedStr('3')+' OR ';
        que:=Copy(que,0,(Length(que)-4));
      end
        else
      begin
        que:=que+' WHERE STATUS2="0" OR STATUS2="4"';
      end;
//      showmessage(que);
      ZQuery2.SQL.Text:=que;
      ZQuery2.Active := True;
      ZQuery2.First;  //tabel
      count:=count+ZQuery2.Fields[0].AsInteger;
      //ShowMessage(IntToStr(count));
      if (ZQuery2.Fields[0].AsInteger>0) then begin
        bTabel[i].ID:=ZQuery1.FieldByName('ID').AsString;
        bTabel[i].TABEL:=ZQuery1.FieldByName('NAMA').AsString;
        bTabel[i].COUNT:=count;
        Inc(i);
      end
        else
      SetLength(bTabel,length(btabel)-1);
      ZQuery1.Next;
    end;
    lblProses.Caption:='';
    jHal:=count div bHal;
    if ((count mod bHal)>0) then jHal:=jHal+1;
    if (gdata and (count>0)) then TampilHalaman(1);
end;

procedure TForm2.FetchDBNama();
var
  kode:String;
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tblName:String;
  i:Integer;
  count:Integer;
  gdata:Boolean;
  que:String;
begin
  lblProses.Caption:='Harap tunggu sedang proses';
  lblHal.Caption:='-';
  gdata:=False;
  for i := 1 to StringGrid1.RowCount - 1
  do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  if (sDesa<>'') then
    begin
      kode:=sDesa;
    end
  else if (sKec<>'') then
    begin
      kode:=sKec;
    end
  else if (sKabkot<>'') then
    begin
      kode:=sKabkot;
    end
  else
    begin
      kode:='';
    end;
  if (kode='') then
    query:='1'
  else
    query:='NAMA LIKE "pemilih_'+kode+'%"';
  ZQuery1.Active := False;
  query:= 'SELECT * FROM penyimpan WHERE '+query;
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //dapat tabel yang di simpan
  SetLength(bTabel,ZQuery1.RecordCount);
  i:=0;
  count:=0;
  while (not ZQuery1.Eof) do
    begin
      gdata:=True;
      ZQuery2.Active := False;
      tblName:=ZQuery1.FieldByName('NAMA').AsString;
      ZQuery2.SQL.Clear;
      que:='SELECT COUNT(*) FROM `'+tblname;
      if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
        que:=que+' WHERE '+
        'NIK LIKE "%'+txtNama.Text+'%" OR '+
        '`NKK` LIKE "%'+txtNama.Text+'%" OR '+
        '`NAMA` LIKE "%'+txtNama.Text+'%" OR '+
        '`TEMPAT` LIKE "%'+txtNama.Text+'%" OR '+
        '`IBU` LIKE "%'+txtNama.Text+'%" OR '+
        '`ALAMAT` LIKE "%'+txtNama.Text+'%" OR '+
        '`DUSUN` LIKE "%'+txtNama.Text+'%" '+
        'AND (';
        if (CheckBox1.Checked) then que:=que+'STATUS2='+QuotedStr('1')+' OR ';
        if (CheckBox2.Checked) then que:=que+'STATUS2='+QuotedStr('2')+' OR ';
        if (CheckBox3.Checked) then que:=que+'STATUS2='+QuotedStr('4')+' OR ';
        if (CheckBox4.Checked) then que:=que+'STATUS2='+QuotedStr('3')+' OR ';
        que:=Copy(que,0,(Length(que)-4));
        que:=que+')';
      end
        else
      begin
        que:=que+'` WHERE '+
        'NIK LIKE "%'+txtNama.Text+'%" OR '+
        '`NKK` LIKE "%'+txtNama.Text+'%" OR '+
        '`NAMA` LIKE "%'+txtNama.Text+'%" OR '+
        '`TEMPAT` LIKE "%'+txtNama.Text+'%" OR '+
        '`IBU` LIKE "%'+txtNama.Text+'%" OR '+
        '`ALAMAT` LIKE "%'+txtNama.Text+'%" OR '+
        '`DUSUN` LIKE "%'+txtNama.Text+'%" '+
        'AND (`STATUS2`="0" OR `STATUS2`="4")';
      end;
//      showmessage(que);
      ZQuery2.SQL.Text:=que;
      ZQuery2.Active := True;
      ZQuery2.First;  //tabel
      count:=count+ZQuery2.Fields[0].AsInteger;
      //ShowMessage(IntToStr(count));
      if (ZQuery2.Fields[0].AsInteger>0) then begin
        bTabel[i].ID:=ZQuery1.FieldByName('ID').AsString;
        bTabel[i].TABEL:=ZQuery1.FieldByName('NAMA').AsString;
        bTabel[i].COUNT:=count;
        Inc(i);
      end
        else
      SetLength(bTabel,length(btabel)-1);
      ZQuery1.Next;
    end;
    lblProses.Caption:='';
    jHal:=count div bHal;
    if ((count mod bHal)>0) then jHal:=jHal+1;
//    ShowMessage(IntToStr(count));
    if (gdata and (count>0)) then TampilHalaman(1);
end;

procedure TForm2.FetchGanda();
var
  kode:String;
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tblName,idsimpan:String;
  i:Integer;
  count:Integer;
  gdata:Boolean;
begin
  lblProses.Caption:='Harap tunggu sedang proses';
  lblHal.Caption:='-';
  gdata:=False;
  for i := 1 to StringGrid1.RowCount - 1
  do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  if (sDesa<>'') then
    begin
      kode:=sDesa;
    end
  else if (sKec<>'') then
    begin
      kode:=sKec;
    end
  else if (sKabkot<>'') then
    begin
      kode:=sKabkot;
    end
  else
    begin
      kode:='';
    end;
  if (kode='') then
    query:='1'
  else
    query:='NAMA LIKE "pemilih_'+kode+'%"';
  ZQuery1.Active := False;
  query:= 'SELECT * FROM penyimpan WHERE '+query;
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //dapat tabel yang di simpan
  SetLength(bTabel,ZQuery1.RecordCount);
  count:=0;
  i:=0;
  while (not ZQuery1.Eof) do
    begin
      gdata:=True;
      ZQuery2.Active := False;
      tblName:=ZQuery1.FieldByName('NAMA').AsString;
      idsimpan:=getIdsimpan(tblname);
      ZQuery2.SQL.Clear;
      if cnama=false then
        ZQuery2.SQL.Text:='SELECT * FROM '+tblname+' WHERE GANDA <> "0"'
      else
        ZQuery2.SQL.Text:='SELECT * FROM '+tblname+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND GANDA <> "0"';
      ZQuery2.Active := True;
      ZQuery2.First;  //tabel
      count:=count+ZQuery2.RecordCount;
      bTabel[i].ID:=ZQuery1.FieldByName('ID').AsString;
      bTabel[i].TABEL:=ZQuery1.FieldByName('NAMA').AsString;
      bTabel[i].COUNT:=count;
      ZQuery1.Next;
      inc(i);
    end;
    lblProses.Caption:='';
    jHal:=1;
//    if ((count mod bHal)>0) then jHal:=jHal+1;
    SetLength(bIdganda,count+1);
    if (gdata and (count>0)) then TampilHalamanGanda(1);
end;

procedure TForm2.GetData(Hal:Integer);
var
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tabel:TTabel;
  tblName,idsimpan,iduser,idganda:String;
  i:Integer;
  vRow,ujung,awal:Integer;
begin
  dRow:=1;
  vRow:=1;
  //cek
  ujung:=Hal*bHal;
  i:=0;
  if (jHal>1) then begin
    for I := 0 to (Length(bTabel)) - 1 do begin
      if (ujung<=bTabel[i].COUNT) then
        break;
    end;
    if (i>0) then begin
      ujung:=ujung-btabel[i-1].COUNT;
      if (ujung<bHal) then begin
        Dec(i);
        awal:=bTabel[i].COUNT-(bHal-ujung);
      end else
        awal:=ujung-bHal;
    end
    else begin
      awal:=ujung-bHal;
    end;
  end
    else
  begin
    awal:=0;
    ujung:=bhal;
  end;
  //showmessage(IntToStr(length(btabel)));
  tblName:=btabel[i].TABEL;
  idsimpan:=btabel[i].ID;
  ZQuery1.Active := False;
  query:= 'SELECT * FROM '+tblName;
  if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
    query:=query+' WHERE ';
    if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
    if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
    if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
    if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
    query:=Copy(query,0,(Length(query)-4));
  end
    else
  begin
    query:=query+' WHERE STATUS2="0" OR STATUS2="4"';
  end;
  query:=query+' ORDER BY NKK ASC LIMIT '+IntToStr(awal)+','+IntToStr(bhal);
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //tabel simpan
  kKota:=Copy(tblName,9,2);
  kKota:=getKabkot(kKota);
  kKec:=Copy(tblName,9,5);
  kKec:=getKec(kKec);
  kDesa:=Copy(tblName,9,8);
  kDesa:=getDesa(kDesa);
  dRow:=dRow+ZQuery1.RecordCount;
  SetLength(bPemilih,dRow);
  while (not ZQuery1.Eof) do begin
    bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
    bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
    bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
    bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
    bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
    bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
    bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
    bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
    bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
    bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
    bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
    bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
    bPemilih[vRow].KOTA:=kKota;
    bPemilih[vRow].KEC:=kKec;
    bPemilih[vRow].DESA:=kDesa;
    bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
    bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
    bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
    bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
    Inc(vrow);
    ZQuery1.Next;
  end;
  if ((ujung<bhal) and (jhal>1)) then begin
    inc(i);
    tblName:=btabel[i].TABEL;
    idsimpan:=btabel[i].ID;
    ZQuery1.Active := False;
    query:= 'SELECT * FROM '+tblName;
    if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
      query:=query+' WHERE ';
      if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
      if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
      if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
      if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
      query:=Copy(query,0,(Length(query)-4));
    end
      else
    begin
      query:=query+' WHERE STATUS2="0" OR STATUS2="4"';
    end;
    query:=query+' ORDER BY NKK ASC LIMIT 0,'+IntToStr(ujung);
    //ShowMessage(query);
    ZQuery1.SQL.Text := query;
    ZQuery1.Active := True;
    ZQuery1.First;  //tabel simpan
    kKota:=Copy(tblName,9,2);
    kKota:=getKabkot(kKota);
    kKec:=Copy(tblName,9,5);
    kKec:=getKec(kKec);
    kDesa:=Copy(tblName,9,8);
    kDesa:=getDesa(kDesa);
    dRow:=dRow+ZQuery1.RecordCount;
    SetLength(bPemilih,dRow);
    while (not ZQuery1.Eof) do begin
      bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
      bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
      bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
      bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
      bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
      bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
      bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
      bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
      bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
      bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
      bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
      bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
      bPemilih[vRow].KOTA:=kKota;
      bPemilih[vRow].KEC:=kKec;
      bPemilih[vRow].DESA:=kDesa;
      bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
      bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
      bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
      bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
      Inc(vrow);
      ZQuery1.Next;
    end;
  end;
end;

procedure TForm2.TampilHalaman(Hal:Integer = 1);
var
  iter,i:Integer;
  tabel,iduser,tanda,query:String;
begin
  txtHal.Enabled:=True;
  lblHal.Caption := IntToStr(Hal)+' of '+IntToStr(jHal);
  cHal:=Hal;
  if (cHal=1) then
    btnPrev.Enabled:=False
  else
    btnPrev.Enabled:=True;
  if (cHal=jHal) then
    btnNext.Enabled:=False
  else
    btnNext.Enabled:=True;
  //get to bpemilih
  if cnama=false then
    GetData(Hal)
  else
    GetDataNama(Hal);
  cRow:=1;
  SetLength(bGanda,0);
  while (cRow<=bHal) and (crow<=High(bpemilih)) do
   begin
    StringGrid1.RowCount:=cRow+1;
    tabel:=getTabel(bPemilih[cRow].ID);
    tabel:=getIdsimpan(tabel);
    iduser:=getIduser(bPemilih[cRow].ID);
    tanda:='';
    with ZQuery1 do begin
      Active:=false;
      sql.Clear;
      query:='SELECT * FROM data_ganda WHERE ID_USER1="'+tabel+'-'+iduser+'"';
      sql.Text:=query;
      Active:=true;
      First;
      if (FieldByName('ID_USER1').AsString<>'') then begin
        tanda:='*';
        while (not Eof) do begin
          SetLength(bGanda,(Length(bGanda)+1));
          bGanda[Length(bGanda)-1].ID1:=FieldByName('ID_USER1').AsString;
          bGanda[Length(bGanda)-1].ID2:=FieldByName('ID_USER2').AsString;
          bGanda[Length(bGanda)-1].KET:=FieldByName('KETERANGAN').AsInteger;
          Next;
        end;
      end;
      //
      Active:=false;
      sql.Clear;
      query:='SELECT * FROM data_ganda WHERE ID_USER2="'+tabel+'-'+iduser+'"';
      sql.Text:=query;
      Active:=true;
      First;
      if (FieldByName('ID_USER2').AsString<>'') then begin
        tanda:='*';
        while (not Eof) do begin
          SetLength(bGanda,(Length(bGanda)+1));
          bGanda[Length(bGanda)-1].ID1:=FieldByName('ID_USER2').AsString;
          bGanda[Length(bGanda)-1].ID2:=FieldByName('ID_USER1').AsString;
          bGanda[Length(bGanda)-1].KET:=FieldByName('KETERANGAN').AsInteger;
          Next;
        end;
      end;
    end;
    StringGrid1.Cells[0,cRow]:=IntToStr((cHal-1)*bHal+cRow)+tanda;
    StringGrid1.Cells[1,cRow]:=bPemilih[cRow].NIK;
    StringGrid1.Cells[2,cRow]:=bPemilih[cRow].NKK;
    StringGrid1.Cells[3,cRow]:=bPemilih[cRow].NAMA;
    StringGrid1.Cells[4,cRow]:=bPemilih[cRow].TEMPAT;
    StringGrid1.Cells[5,cRow]:=bPemilih[cRow].TANGGAL;
    StringGrid1.Cells[6,cRow]:=bPemilih[cRow].IBU;
    StringGrid1.Cells[7,cRow]:=bPemilih[cRow].AGAMA;
    StringGrid1.Cells[8,cRow]:=bPemilih[cRow].KAWIN;
    StringGrid1.Cells[9,cRow]:=bPemilih[cRow].KELAMIN;
    StringGrid1.Cells[10,cRow]:=bPemilih[cRow].KELAINAN;
    StringGrid1.Cells[11,cRow]:=bPemilih[cRow].ALAMAT;
    StringGrid1.Cells[12,cRow]:=bPemilih[cRow].KOTA;
    StringGrid1.Cells[13,cRow]:=bPemilih[cRow].KEC;
    StringGrid1.Cells[14,cRow]:=bPemilih[cRow].DESA;
    StringGrid1.Cells[15,cRow]:=bPemilih[cRow].RW;
    StringGrid1.Cells[16,cRow]:=bPemilih[cRow].RT;
    StringGrid1.Cells[17,cRow]:=bPemilih[cRow].DUSUN;
    StringGrid1.Cells[18,cRow]:=bPemilih[cRow].KET;
    Inc(crow);
   end;
   //autosize
   for i := 0 to StringGrid1.ColCount - 1 do
    AutoSizeCol(StringGrid1, i);
end;

procedure TForm2.GetDataNama(Hal:Integer);
var
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tabel:TTabel;
  tblName,idsimpan,iduser,idganda:String;
  i:Integer;
  vRow,ujung,awal:Integer;
begin
  dRow:=1;
  vRow:=1;
  //cek
  ujung:=Hal*bHal;
  i:=0;
  if (jHal>1) then begin
    for I := 0 to (Length(bTabel)) - 1 do begin
      if (ujung<=bTabel[i].COUNT) then
        break;
    end;
    if (i>0) then begin
      ujung:=ujung-btabel[i-1].COUNT;
      if (ujung<bHal) then begin
        Dec(i);
        awal:=bTabel[i].COUNT-(bHal-ujung);
      end else
        awal:=ujung-bHal;
    end
    else begin
      awal:=ujung-bHal;
    end;
  end
    else
  begin
    awal:=0;
    ujung:=bhal;
  end;
  //showmessage(IntToStr(length(btabel)));
  tblName:=btabel[i].TABEL;
  idsimpan:=btabel[i].ID;
  ZQuery1.Active := False;
  query:= 'SELECT * FROM '+tblName;
  if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
    query:=query+' WHERE '+
    'NIK LIKE "%'+txtNama.Text+'%" OR '+
    'NKK LIKE "%'+txtNama.Text+'%" OR '+
    'NAMA LIKE "%'+txtNama.Text+'%" OR '+
    'TEMPAT LIKE "%'+txtNama.Text+'%" OR '+
    'IBU LIKE "%'+txtNama.Text+'%" OR '+
    'ALAMAT LIKE "%'+txtNama.Text+'%" OR '+
    'DUSUN LIKE "%'+txtNama.Text+'%" '+
    'AND (';
    if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
    if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
    if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
    if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
    query:=Copy(query,0,(Length(query)-4));
    query:=query+')';
  end
    else
  begin
    query:=query+' WHERE '+
    'NIK LIKE "%'+txtNama.Text+'%" OR '+
    'NKK LIKE "%'+txtNama.Text+'%" OR '+
    'NAMA LIKE "%'+txtNama.Text+'%" OR '+
    'TEMPAT LIKE "%'+txtNama.Text+'%" OR '+
    'IBU LIKE "%'+txtNama.Text+'%" OR '+
    'ALAMAT LIKE "%'+txtNama.Text+'%" OR '+
    'DUSUN LIKE "%'+txtNama.Text+'%" '+
    'AND (STATUS2="0" OR STATUS2="4")';
  end;
  query:=query+' ORDER BY NKK ASC LIMIT '+IntToStr(awal)+','+IntToStr(bhal);
//  ShowMessage(query);
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //tabel simpan
  kKota:=Copy(tblName,9,2);
  kKota:=getKabkot(kKota);
  kKec:=Copy(tblName,9,5);
  kKec:=getKec(kKec);
  kDesa:=Copy(tblName,9,8);
  kDesa:=getDesa(kDesa);
  dRow:=dRow+ZQuery1.RecordCount;
  SetLength(bPemilih,dRow);
  while (not ZQuery1.Eof) do begin
    bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
    bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
    bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
    bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
    bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
    bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
    bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
    bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
    bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
    bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
    bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
    bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
    bPemilih[vRow].KOTA:=kKota;
    bPemilih[vRow].KEC:=kKec;
    bPemilih[vRow].DESA:=kDesa;
    bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
    bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
    bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
    bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
    Inc(vrow);
    ZQuery1.Next;
  end;
  if ((ujung<bhal) and (jhal>1)) then begin
    inc(i);
    tblName:=btabel[i].TABEL;
    idsimpan:=btabel[i].ID;
    ZQuery1.Active := False;
    query:= 'SELECT * FROM '+tblName;
    if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
      query:=query+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (';
      if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
      if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
      if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
      if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
      query:=Copy(query,0,(Length(query)-4));
      query:=query+')';
    end
      else
    begin
      query:=query+' WHERE '+
      'NIK LIKE "%'+txtNama.Text+'%" OR '+
      'NKK LIKE "%'+txtNama.Text+'%" OR '+
      'NAMA LIKE "%'+txtNama.Text+'%" OR '+
      'TEMPAT LIKE "%'+txtNama.Text+'%" OR '+
      'IBU LIKE "%'+txtNama.Text+'%" OR '+
      'ALAMAT LIKE "%'+txtNama.Text+'%" OR '+
      'DUSUN LIKE "%'+txtNama.Text+'%" '+
      'AND (STATUS2="0" OR STATUS2="4")';
    end;
    query:=query+' ORDER BY NKK ASC LIMIT 0,'+IntToStr(ujung);
//    ShowMessage(query);
    ZQuery1.SQL.Text := query;
    ZQuery1.Active := True;
    ZQuery1.First;  //tabel simpan
    kKota:=Copy(tblName,9,2);
    kKota:=getKabkot(kKota);
    kKec:=Copy(tblName,9,5);
    kKec:=getKec(kKec);
    kDesa:=Copy(tblName,9,8);
    kDesa:=getDesa(kDesa);
    dRow:=dRow+ZQuery1.RecordCount;
    SetLength(bPemilih,dRow);
    while (not ZQuery1.Eof) do begin
      bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
      bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
      bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
      bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
      bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
      bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
      bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
      bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
      bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
      bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
      bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
      bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
      bPemilih[vRow].KOTA:=kKota;
      bPemilih[vRow].KEC:=kKec;
      bPemilih[vRow].DESA:=kDesa;
      bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
      bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
      bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
      bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
      Inc(vrow);
      ZQuery1.Next;
    end;
  end;
end;

procedure TForm2.TampilHalamanGanda(Hal:Integer = 1);
var
  iter,i:Integer;
  tabel,iduser,query,kkota,kkec,kdesa:String;
  terus:Boolean;
begin
  txtHal.Enabled:=True;
  lblHal.Caption := IntToStr(Hal)+' of '+IntToStr(jHal);
  cHal:=Hal;
  if (cHal=1) then
    btnPrev.Enabled:=False
  else
    btnPrev.Enabled:=True;
  if (cHal=jHal) then
    btnNext.Enabled:=False
  else
    btnNext.Enabled:=True;
  //get to bpemilih
  cRow:=1;
  SetLength(bGanda,0);
  for i := low(btabel) to high(btabel) do
   begin
    tabel:=btabel[i].TABEL;
    kKota:=Copy(tabel,9,2);
    kKota:=getKabkot(kKota);
    kKec:=Copy(tabel,9,5);
    kKec:=getKec(kKec);
    kDesa:=Copy(tabel,9,8);
    kDesa:=getDesa(kDesa);
    with zquery2 do begin
      Active:=false;
      sql.Clear;
      if cnama=false then
        sql.Text:='SELECT * FROM '+tabel+' WHERE GANDA <> "0"'
      else
        sql.Text:='SELECT * FROM '+tabel+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND GANDA <> "0"';
      active:=true;
      first;
    end;
    while (not zquery2.Eof) do begin
      iduser:=zquery2.FieldByName('ID_USER').AsString;
      terus:=false;
        zquery1.Active:=false;
        zquery1.sql.Clear;
        //
        if cbGanda.ItemIndex=0 then
          query:='SELECT * FROM data_ganda WHERE ID_USER1="'+getidsimpan(tabel)+'-'+iduser+'"'
        else
          query:='SELECT * FROM data_ganda WHERE ID_USER1="'+getidsimpan(tabel)+'-'+iduser+'" AND KETERANGAN='+QuotedStr(IntToStr(cbGanda.ItemIndex));
        //
//        ShowMessage(query);
        zquery1.sql.Text:=query;
        zquery1.Active:=true;
        zquery1.First;
        if (zquery1.FieldByName('ID_USER1').AsString<>'') then begin
          terus:=true;
          while (not zquery1.Eof) do begin
            SetLength(bGanda,(Length(bGanda)+1));
            bGanda[Length(bGanda)-1].ID1:=zquery1.FieldByName('ID_USER1').AsString;
            bGanda[Length(bGanda)-1].ID2:=zquery1.FieldByName('ID_USER2').AsString;
            bGanda[Length(bGanda)-1].KET:=zquery1.FieldByName('KETERANGAN').AsInteger;
            zquery1.Next;
          end;
        end;
        //
        zquery1.Active:=false;
        zquery1.sql.Clear;
        //
        if cbGanda.ItemIndex=0 then
          query:='SELECT * FROM data_ganda WHERE ID_USER2="'+getidsimpan(tabel)+'-'+iduser+'"'
        else
          query:='SELECT * FROM data_ganda WHERE ID_USER2="'+getidsimpan(tabel)+'-'+iduser+'" AND KETERANGAN='+QuotedStr(IntToStr(cbGanda.ItemIndex));
        //
        zquery1.sql.Text:=query;
        zquery1.Active:=true;
        zquery1.First;
        if (zquery1.FieldByName('ID_USER2').AsString<>'') then begin
          terus:=true;
          while (not zquery1.Eof) do begin
            SetLength(bGanda,(Length(bGanda)+1));
            bGanda[Length(bGanda)-1].ID1:=zquery1.FieldByName('ID_USER2').AsString;
            bGanda[Length(bGanda)-1].ID2:=zquery1.FieldByName('ID_USER1').AsString;
            bGanda[Length(bGanda)-1].KET:=zquery1.FieldByName('KETERANGAN').AsInteger;
            zquery1.Next;
          end;
        end;
       if terus=true then begin
          StringGrid1.RowCount:=cRow+1;
          StringGrid1.Cells[0,cRow]:=IntToStr(cRow)+'*';
          StringGrid1.Cells[1,cRow]:=zquery2.FieldByName('NIK').AsString;
          StringGrid1.Cells[2,cRow]:=zquery2.FieldByName('NKK').AsString;
          StringGrid1.Cells[3,cRow]:=zquery2.FieldByName('NAMA').AsString;
          StringGrid1.Cells[4,cRow]:=zquery2.FieldByName('TEMPAT').AsString;
          StringGrid1.Cells[5,cRow]:=zquery2.FieldByName('TANGGAL').AsString;
          StringGrid1.Cells[6,cRow]:=zquery2.FieldByName('IBU').AsString;
          StringGrid1.Cells[7,cRow]:=zquery2.FieldByName('AGAMA').AsString;
          StringGrid1.Cells[8,cRow]:=zquery2.FieldByName('KAWIN').AsString;
          StringGrid1.Cells[9,cRow]:=zquery2.FieldByName('KELAMIN').AsString;
          StringGrid1.Cells[10,cRow]:=zquery2.FieldByName('KELAINAN').AsString;
          StringGrid1.Cells[11,cRow]:=zquery2.FieldByName('ALAMAT').AsString;
          StringGrid1.Cells[12,cRow]:=kkota;
          StringGrid1.Cells[13,cRow]:=kkec;
          StringGrid1.Cells[14,cRow]:=kdesa;
          StringGrid1.Cells[15,cRow]:=zquery2.FieldByName('RW').AsString;
          StringGrid1.Cells[16,cRow]:=zquery2.FieldByName('RT').AsString;
          StringGrid1.Cells[17,cRow]:=zquery2.FieldByName('DUSUN').AsString;
          StringGrid1.Cells[18,cRow]:=zquery2.FieldByName('KET').AsString;
          bidganda[crow]:=tabel+iduser;
          Inc(crow);
       end;
      zquery2.Next;
    end;
   end;
   //autosize
   for i := 0 to StringGrid1.ColCount - 1 do
    AutoSizeCol(StringGrid1, i);
end;

function TForm2.cekBGanda(id:String):Boolean;
var
  res:Boolean;
  temp:TGanda;
begin
  res:=false;
{  for temp in bGanda do begin
    if (temp.ID1=id) then begin
      res:=True;
      break;
    end;
  end;}
  Result:=res;
end;

function TForm2.getTabel(id:String):String;
begin
  Result:=Copy(id,1,16);
end;

function TForm2.getIduser(id:String):String;
begin
  Result:=Copy(id,17,(Length(id)-16));
end;

function TForm2.getDesa(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bDesa) - 1 do begin
    if (bDesa[i].KODE=kode) then
      break;
  end;
    Result:=bDesa[i].NAMA;
end;

function TForm2.getKec(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bKec) - 1 do begin
    if (bKec[i].KODE=kode) then
      break;
  end;
    Result:=bKec[i].NAMA;
end;

function TForm2.getKabkot(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bKabkot) - 1 do begin
    if (bKabkot[i].KODE=kode) then
      break;
  end;
    Result:=bKabkot[i].NAMA;
end;

function TForm2.getIdsimpan(tabel:String):String;
var
  I:integer;
  res:String;
begin
  res:='';
  for I := 0 to Length(bPenyimpan) - 1 do begin
    if (bPenyimpan[i].NAMA=tabel) then begin
      res:=bPenyimpan[i].ID;
      break;
    end;
  end;
    Result:=res;
end;

function TForm2.getSimpan(id:String):String;
var
  I:integer;
  res:String;
begin
  res:='';
  for I := 0 to Length(bPenyimpan) - 1 do begin
    if (bPenyimpan[i].ID=id) then begin
      res:=bPenyimpan[i].NAMA;
      break;
    end;
  end;
    Result:=res;
end;

procedure TForm2.txtHalKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key = VK_RETURN then
    getPage;
end;

procedure TForm2.txtNamaChange(Sender: TObject);
begin
  if txtNama.Text<>'' then
    btnCarinama.Enabled:=true
  else
    btnCarinama.Enabled:=false;
end;

procedure TForm2.getPage();
begin
    if ((txtHal.Text='') or (StrToInt(txtHal.Text)>jHal) or (StrToInt(txtHal.Text)<1)) then
      ShowMessage('Isi dahulu dengan halaman yang sesuai')
    else if (jHal=0) then
      ShowMessage('Tidak ada halaman yang dapat ditampilkan')
    else
      begin
        TampilHalaman(StrToInt(txtHal.Text));
      end;
end;

procedure TForm2.StringGrid1Click(Sender: TObject);
var
  pos,tots,i:Integer;
  tid:String;
begin
  if (StringGrid1.Row>0) then begin
      btnEdit.Enabled:=True;
      btnTanda.Enabled:=True;
      pos:=StringGrid1.Row;
      //getID
      if (not ckDataGanda.Checked) then begin
        ptable:=Copy(bPemilih[pos].ID,1,16);
        tots:=Length(bPemilih[pos].ID)-16;
        pid:=Copy(bPemilih[pos].ID,17,tots);
      end
        else
      begin
        ptable:=Copy(bidganda[pos],1,16);
        tots:=Length(bidganda[pos])-16;
        pid:=Copy(bidganda[pos],17,tots);
      end;
      tid:=getIdsimpan(ptable);
      btnTanda2.Enabled:=false;
      btnEdit2.Enabled:=false;
      for i := 1 to StringGrid2.RowCount - 1
      do StringGrid2.Rows[i].Clear;
      StringGrid2.RowCount:=2;
      if (cekBGanda(tid+'-'+pid)) then
         showGanda(tid+'-'+pid);
  end;
end;

procedure TForm2.StringGrid2Click(Sender: TObject);
var
  pos,tots,i:Integer;
begin
  if (StringGrid2.Row>0) then begin
      btnTanda2.Enabled:=True;
      btnEdit2.Enabled:=True;
      pos:=StringGrid2.Row;
      //getID
      ptable2:=bgridganda[pos].nama;
      pid2:=bgridganda[pos].id;
  end;
end;

procedure TForm2.showGanda(idganda:String);
var
  i,srow:Integer;
  tabel,tid,pid,id2,id1:String;
  idkabkot,idkec,idesa:String;
  temp:TStrings;
begin
  srow:=2;
  SetLength(bGridganda,srow);
  for I := 0 to Length(bGanda) - 1 do begin
    if (bGanda[i].ID1=idganda) then begin
      id2:=bganda[i].ID2;
      temp := TStringList.Create;
      try
        ExtractStrings(['-'], [], PChar(id2), temp);
        tid := temp[0];
        pid := temp[1];
      finally
        temp.Free;
      end;
    
      tabel:=getSimpan(tid);
      idkabkot:=Copy(tabel,9,2);
      idkec:=Copy(tabel,9,5);
      idesa:=Copy(tabel,9,8);
      ZTable2.Active:=false;
      ZTable2.TableName:=tabel;
      ZTable2.Filter:='ID_USER='+QuotedStr(pid);
      ZTable2.Filtered:=true;
      ZTable2.active:=true;
      ZTable2.First;
        StringGrid2.Cells[0,srow-1]:=IntToStr(sRow-1);
        StringGrid2.Cells[1,srow-1]:=ZTable2.FieldByName('NIK').AsString;
        StringGrid2.Cells[2,srow-1]:=ZTable2.FieldByName('NKK').AsString;
        StringGrid2.Cells[3,srow-1]:=ZTable2.FieldByName('NAMA').AsString;
        StringGrid2.Cells[4,srow-1]:=ZTable2.FieldByName('TEMPAT').AsString;
        StringGrid2.Cells[5,srow-1]:=ZTable2.FieldByName('TANGGAL').AsString;
        StringGrid2.Cells[6,srow-1]:=ZTable2.FieldByName('IBU').AsString;
        StringGrid2.Cells[7,srow-1]:=ZTable2.FieldByName('AGAMA').AsString;
        StringGrid2.Cells[8,srow-1]:=ZTable2.FieldByName('KAWIN').AsString;
        StringGrid2.Cells[9,srow-1]:=ZTable2.FieldByName('KELAMIN').AsString;
        StringGrid2.Cells[10,srow-1]:=ZTable2.FieldByName('KELAINAN').AsString;
        StringGrid2.Cells[11,srow-1]:=ZTable2.FieldByName('ALAMAT').AsString;
        StringGrid2.Cells[12,srow-1]:=getKabkot(idkabkot);
        StringGrid2.Cells[13,srow-1]:=getKec(idkec);
        StringGrid2.Cells[14,srow-1]:=getDesa(idesa);
        StringGrid2.Cells[15,srow-1]:=ZTable2.FieldByName('RW').AsString;
        StringGrid2.Cells[16,srow-1]:=ZTable2.FieldByName('RT').AsString;
        StringGrid2.Cells[17,srow-1]:=ZTable2.FieldByName('DUSUN').AsString;
        StringGrid2.Cells[18,srow-1]:=ZTable2.FieldByName('KET').AsString;
        bGridganda[srow-1].ID:=pid;
        bGridganda[srow-1].NAMA:=tabel;
      Inc(srow);
      StringGrid2.RowCount:=srow;
    end;
  end;
  for i := 0 to StringGrid2.ColCount - 1 do
      AutoSizeCol(StringGrid2, i);
end;

procedure TForm2.AutoSizeCol(Grid: TStringGrid; Column: integer);
var
  i, W, WMax: integer;
begin
  WMax := 0;
  for i := 0 to (Grid.RowCount - 1) do begin
    W := Grid.Canvas.TextWidth(Grid.Cells[Column, i]);
    if W > WMax then
      WMax := W;
  end;
  Grid.ColWidths[Column] := WMax + 5;
end;

procedure TForm2.FormCreate(Sender: TObject);
var
  I: Integer;
  setting:TSetting_Form;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  ztable2.Connection:=ZConnection1;
  ZQuery2.Connection:=ZConnection1;
  ZConnection1.LoginPrompt:=False;
  ZConnection1.Connected := True;
  //
  sKabkot:='';
  sKec:='';
  sDesa:='';
  lblProses.Caption:='';
  cRow:=2;  //wadah row
  dRow:=1;  //menyimpan banyaknya data
  bHal:=25;  //pemilih per halaman
  lblHal.Caption:= '-';
  btnCari.Enabled:=False;
  txtHal.Enabled:=False;
  btnPrev.Enabled:=False;
  btnNext.Enabled:=False;
  btnEdit.Enabled:=False;
  btnEdit2.Enabled:=false;
  btnTanda.Enabled:=False;
  btnTanda2.Enabled:=False;
  btnBersih.Enabled:=False;
  btnCarinama.Enabled:=False;
  btnRefresh.Enabled:=false;
  //
  cbGanda.Enabled:=False;
  cbGanda.Clear;
  cbGanda.Items.Add('Semua Level');
  cbGanda.Items.Add('Level 1');
  cbGanda.Items.Add('Level 2');
  cbGanda.Items.Add('Level 3');
  //
  //grid
  StringGrid1.Cells[0,0]:='NO';
  StringGrid1.Cells[1,0]:='NIK';
  StringGrid1.Cells[2,0]:='NKK';
  StringGrid1.Cells[3,0]:='Nama';
  StringGrid1.Cells[4,0]:='Tempat Lahir';
  StringGrid1.Cells[5,0]:='Tanggal Lahir';
  StringGrid1.Cells[6,0]:='Nama Ibu';
  StringGrid1.Cells[7,0]:='Agama';
  StringGrid1.Cells[8,0]:='Perkawinan';
  StringGrid1.Cells[9,0]:='Jenis Kelamin';
  StringGrid1.Cells[10,0]:='Kelainan';
  StringGrid1.Cells[11,0]:='Alamat';
  StringGrid1.Cells[12,0]:='Kota';
  StringGrid1.Cells[13,0]:='Kecamatan';
  StringGrid1.Cells[14,0]:='Desa';
  StringGrid1.Cells[15,0]:='RW';
  StringGrid1.Cells[16,0]:='RT';
  StringGrid1.Cells[17,0]:='Dusun';
  StringGrid1.Cells[18,0]:='Ket';
  StringGrid1.RowCount:=cRow;
  //grid2
  StringGrid2.Cells[0,0]:='NO';
  StringGrid2.Cells[1,0]:='NIK';
  StringGrid2.Cells[2,0]:='NKK';
  StringGrid2.Cells[3,0]:='Nama';
  StringGrid2.Cells[4,0]:='Tempat Lahir';
  StringGrid2.Cells[5,0]:='Tanggal Lahir';
  StringGrid2.Cells[6,0]:='Nama Ibu';
  StringGrid2.Cells[7,0]:='Agama';
  StringGrid2.Cells[8,0]:='Perkawinan';
  StringGrid2.Cells[9,0]:='Jenis Kelamin';
  StringGrid2.Cells[10,0]:='Kelainan';
  StringGrid2.Cells[11,0]:='Alamat';
  StringGrid2.Cells[12,0]:='Kota';
  StringGrid2.Cells[13,0]:='Kecamatan';
  StringGrid2.Cells[14,0]:='Desa';
  StringGrid2.Cells[15,0]:='RW';
  StringGrid2.Cells[16,0]:='RT';
  StringGrid2.Cells[17,0]:='Dusun';
  StringGrid2.Cells[18,0]:='Ket';
  StringGrid2.RowCount:=cRow;
  //bpenyimpan
  with ZTable1 do begin
    Active:=False;
    TableName:='penyimpan';
    Active:=True;
    SetLength(bPenyimpan,RecordCount);
    I:=0;
    while (not Eof) do begin
      bPenyimpan[I].ID:=FieldByName('ID').AsString;
      bPenyimpan[I].NAMA:=FieldByName('NAMA').AsString;
      Inc(I);
      Next;
    end;
  end;
  //desa
  with ZTable1 do begin
    Active:=False;
    TableName:='desa';
    Active:=True;
    SetLength(bDesa,RecordCount);
    I:=0;
    while (not Eof) do begin
      bDesa[I].KODE:=FieldByName('KODE_DESA').AsString;
      bDesa[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
  end;
  //kec
  with ZTable1 do begin
    Active:=False;
    TableName:='kecamatan';
    Active:=True;
    SetLength(bKec,RecordCount);
    I:=0;
    while (not Eof) do begin
      bKec[I].KODE:=FieldByName('KODE_KECAMATAN').AsString;
      bKec[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
  end;
  //kota
  with ZTable1 do begin
    Active:=False;
    TableName:='kabkot';
    Active:=True;
    SetLength(bKabkot,RecordCount);
    I:=0;
    while (not Eof) do begin
      bKabkot[I].KODE:=FieldByName('KODE').AsString;
      bKabkot[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
    ComboBox1.Clear;
    ComboBox1.Items.Add('SEMUA KAB/KOTA');
    for I := 0 to Length(bKabkot) - 1 do
      begin
        ComboBox1.Items.Add(bKabkot[i].NAMA);
      end;
    ComboBox1.ItemIndex:=0;
  end;
end;

end.

